//
//  SAFMDBManger.m
//  Saas
//
//  Created by 黄志刚 on 2022/2/16.
//  Copyright © 2022 bing. All rights reserved.
//

#import "SAFMDBManger.h"
#import "SADataModel.h"
#import "FMDatabase.h"
#import "SASalesOrderViewListModel.h"
#import "FMDatabaseQueue.h"

@interface SAFMDBManger ()

@end

@implementation SAFMDBManger

+ (SAFMDBManger *)sharedInstance
{
    static SAFMDBManger *FMDBManager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        FMDBManager = [[SAFMDBManger alloc] init];
    });
    return FMDBManager;
}

- (BOOL)coverData:(NSMutableArray<SADataModel *> *)dataMarr {
    BOOL ret = NO;
    NSString *dbPath = [self getDbPath];
    NSString *tableName = @"SAProduct";
    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
    if ([dataBase open]) {
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@",tableName];
        ret = [dataBase executeUpdate:deleteSql];
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'sequence' integer, 'productId' TEXT, 'merchantId' TEXT, 'orgMerchantId' TEXT, 'productName' TEXT, 'coverUrl' TEXT, 'phoneticInitial' TEXT, 'phoneticInitialAll' TEXT,  'productCode' TEXT, 'barcode' TEXT, 'entryPrice' TEXT, 'wholesalePrice' TEXT, 'retailPrice' TEXT, 'merchantPrice' TEXT, 'onsales' TEXT, 'createTime' TEXT, 'updateTime' TEXT, 'unitId' TEXT, 'season' TEXT, 'brand' TEXT, 'gender' TEXT, 'category' TEXT, 'constituent' TEXT)",tableName];
        ret = [dataBase executeUpdate:sql];
        
        if (ret) {
            [SAValueManger shareInStance].operationSqlite = YES;
            //NSLog(@"**********开始事务操作*************");
            FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
            dispatch_queue_t q1 = dispatch_queue_create("queue1", NULL);
            
            dispatch_async(q1, ^{
                [queue inDatabase:^(FMDatabase *db2) {
                    [dataBase shouldCacheStatements]; // 开启缓存
                    [dataBase beginTransaction]; // 事务操作
                    BOOL isRollBack = NO;
                    @try {
                        long i = 1;
                        for(SADataModel *model in dataMarr) {
                            BOOL flag = [dataBase executeUpdate:@"insert into SAProduct (sequence,productId,merchantId,orgMerchantId,productName ,coverUrl ,phoneticInitial ,phoneticInitialAll,productCode,barcode,entryPrice ,wholesalePrice,retailPrice,merchantPrice,onsales,createTime,updateTime,unitId,season,brand,gender,category,constituent) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",[NSNumber numberWithLong:i],model.productId,model.merchantId,model.orgMerchantId,model.productName,model.coverUrl,model.phoneticInitial,model.phoneticInitialAll,model.productCode,model.barcode,model.entryPrice,model.wholesalePrice,model.retailPrice,model.merchantPrice,model.onsales,model.createTime,model.updateTime,model.unitId,model.season,model.brand,model.gender,model.category,model.constituent];
                            i++;
                            //                        if (flag) {
                            //                            NSLog(@"插入成功");
                            //                        }
                        }
                    } @catch (NSException *exception) {
                        isRollBack =YES;
                        [dataBase rollback];// 回滚
                    } @finally {
                        if(!isRollBack) {
                            [dataBase commit];
                        }
                        [dataBase close];
                        [SAValueManger shareInStance].operationSqlite = NO;
                    }
                    
                }];
                
            });
        }else {
            [dataBase close];
        }
    }
    return ret;
}

-(NSString *)getUpdateTime {
    BOOL ret = NO;
    NSString *dbPath = [self getDbPath];
    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
    NSString *tableName = @"SAProduct";
    NSString *updateTime = @"2000-01-11 07:49:19";//设置开始时间
    if ([dataBase open]) {
        //        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@",tableName];
        //        ret = [dataBase executeUpdate:deleteSql]; //删除表 调试使用
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'sequence' integer, 'productId' TEXT, 'merchantId' TEXT, 'orgMerchantId' TEXT, 'productName' TEXT, 'coverUrl' TEXT, 'phoneticInitial' TEXT, 'phoneticInitialAll' TEXT, 'productCode' TEXT, 'barcode' TEXT, 'entryPrice' TEXT, 'wholesalePrice' TEXT, 'retailPrice' TEXT, 'merchantPrice' TEXT, 'onsales' TEXT, 'createTime' TEXT, 'updateTime' TEXT, 'unitId' TEXT, 'season' TEXT, 'brand' TEXT, 'gender' TEXT, 'category' TEXT, 'constituent' TEXT)",tableName];
        ret = [dataBase executeUpdate:sql];
        
        if (ret) {
            NSString *readSql = [NSString stringWithFormat:@"select * from %@ order by updateTime desc",tableName];
            FMResultSet *result = [dataBase executeQuery:readSql];
            while ([result next]) {
                updateTime = [result stringForColumn:@"updateTime"];
                break;
            }
        }
    }
    [dataBase close];
    return updateTime;
}

- (BOOL)updateData:(NSMutableArray<SADataModel *> *)dataMarr AndIds:(NSString *)ids
{
    BOOL ret = NO;
    NSString *dbPath = [self getDbPath];
    NSString *tableName = @"SAProduct";
    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
    if ([dataBase open]) {
        //        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@",tableName];
        //        ret = [dataBase executeUpdate:deleteSql];
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'sequence' integer, 'productId' TEXT, 'merchantId' TEXT, 'orgMerchantId' TEXT, 'productName' TEXT, 'coverUrl' TEXT, 'phoneticInitial' TEXT, 'phoneticInitialAll' TEXT,  'productCode' TEXT, 'barcode' TEXT, 'entryPrice' TEXT, 'wholesalePrice' TEXT, 'retailPrice' TEXT, 'merchantPrice' TEXT, 'onsales' TEXT, 'createTime' TEXT, 'updateTime' TEXT, 'unitId' TEXT, 'season' TEXT, 'brand' TEXT, 'gender' TEXT, 'category' TEXT, 'constituent' TEXT)",tableName];
        ret = [dataBase executeUpdate:sql];
        if (ids.length > 0) {
            NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ where productId in (%@)",tableName,ids];
            ret = [dataBase executeUpdate:deleteSql];
        }
        if (ret) {
            [SAValueManger shareInStance].operationSqlite = YES;
            //NSLog(@"**********开始事务操作*************");
            [dataBase shouldCacheStatements]; // 开启缓存
            [dataBase beginTransaction]; // 事务操作
            BOOL isRollBack = NO;
            @try{
                long i = 1;
                for(SADataModel *model in dataMarr) {
                    BOOL flag = [dataBase executeUpdate:@"insert into SAProduct (sequence,productId,merchantId,orgMerchantId,productName ,coverUrl ,phoneticInitial ,phoneticInitialAll,productCode,barcode,entryPrice ,wholesalePrice,retailPrice,merchantPrice,onsales,createTime,updateTime,unitId,season,brand,gender,category,constituent) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",[NSNumber numberWithLong:i],model.productId,model.merchantId,model.orgMerchantId,model.productName,model.coverUrl,model.phoneticInitial,model.phoneticInitialAll,model.productCode,model.barcode,model.entryPrice,model.wholesalePrice,model.retailPrice,model.merchantPrice,model.onsales,model.createTime,model.updateTime,model.unitId,model.season,model.brand,model.gender,model.category,model.constituent];
                    i++;
                    //                        if (flag) {
                    //                            NSLog(@"插入成功");
                    //                        }
                }
            }
            @catch(NSException *exception) {
                isRollBack =YES;
                [dataBase rollback];// 回滚
            }
            @finally{
                if(!isRollBack) {
                    [dataBase commit];
                }
                [dataBase close];
                [SAValueManger shareInStance].operationSqlite = NO;
            }
        }else {
            [dataBase close];
        }
    }
    
    return ret;
}

- (BOOL)readAllDataWithMdic:(NSMutableDictionary *)mdic CurrentPage:(NSInteger)currentPage Sort:(NSString *)sort SearchStr:(NSString *)searchStr AndDataArr:(getAllData)allDataBlock {
    BOOL ret = NO;
    NSString *dbPath = [self getDbPath];
    NSString *tableName = @"SAProduct";
    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
    if ([dataBase open]) {
        NSString *sql =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'sequence' integer, 'productId' TEXT, 'merchantId' TEXT, 'orgMerchantId' TEXT, 'productName' TEXT, 'coverUrl' TEXT, 'phoneticInitial' TEXT, 'phoneticInitialAll' TEXT,  'productCode' TEXT, 'barcode' TEXT, 'entryPrice' TEXT, 'wholesalePrice' TEXT, 'retailPrice' TEXT, 'merchantPrice' TEXT, 'onsales' TEXT, 'createTime' TEXT, 'updateTime' TEXT, 'unitId' TEXT, 'season' TEXT, 'brand' TEXT, 'gender' TEXT, 'category' TEXT, 'constituent' TEXT)",tableName];
        ret = [dataBase executeUpdate:sql];
        if (ret) {
            __block NSString *conditionsSql = @"";
            NSString *sortSql = @"order by updateTime desc";
            if ([sort isEqualToString:@"2"]) {
                sortSql = @"order by updateTime";
            }
            NSString *page = [NSString stringWithFormat:@"limit 300 offset %0.f",(currentPage - 1) * 300.0];
            
            NSString *readSql = @"";
            
            if (mdic.count > 0) {
                [mdic enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
                    if (conditionsSql.length > 0) {
                        conditionsSql = [NSString stringWithFormat:@"%@ and %@ = %@",conditionsSql, key ,obj];
                    }else {
                        conditionsSql = [NSString stringWithFormat:@"%@ = %@", key ,obj];
                    }
                }];
                if (searchStr.length > 0) {
                    searchStr = [NSString stringWithFormat:@"%%%@%%",searchStr];
                    readSql = [NSString stringWithFormat:@"select * from %@ where %@ and (productCode like '%@' or productName like '%@' or phoneticInitial like '%@' or phoneticInitialAll like '%@' %@) %@",tableName,conditionsSql,searchStr,searchStr,searchStr,searchStr,sortSql,page];
                }else {
                    readSql = [NSString stringWithFormat:@"select * from %@ where %@ %@ %@",tableName,conditionsSql,sortSql,page];
                }
            }else {
                if (searchStr.length > 0) {
                    searchStr = [NSString stringWithFormat:@"%%%@%%",searchStr];
                    readSql = [NSString stringWithFormat:@"select * from %@ where productCode like '%@' or productName like '%@' or phoneticInitial like '%@' or phoneticInitialAll like '%@' %@ %@",tableName,searchStr,searchStr,searchStr,searchStr,sortSql,page];
                }else {
                    readSql = [NSString stringWithFormat:@"select * from %@ %@ %@",tableName ,sortSql, page];
                }
            }
            
            FMResultSet *result = [dataBase executeQuery:readSql];
            NSMutableArray<SASalesOrderViewListModel *> *dataMarr = [NSMutableArray array];
            while ([result next]) {
                    SASalesOrderViewListModel *model = [SASalesOrderViewListModel new];
                    model.productId = [result stringForColumn:@"productId"];
                    model.img = [result stringForColumn:@"coverUrl"];
                    model.productName = [result stringForColumn:@"productName"];
                    model.productCode = [result stringForColumn:@"productCode"];
                    model.retailPrice = [result stringForColumn:@"retailPrice"];
                    model.wholesalePrice = [result stringForColumn:@"wholesalePrice"];
                    model.merchantPrice = [result stringForColumn:@"merchantPrice"];
                    [dataMarr addObject:model];
            }
            allDataBlock(dataMarr);
        }
    }
    return ret;
}

- (BOOL)updatePhoneData:(NSString *)phone {
    BOOL ret = NO;
    NSString *dbPath = [self getPhoneDbPath];
    NSString *tableName = @"phoneTable";
    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
    if ([dataBase open]) {
        NSString *sql =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'phone' TEXT)",tableName];
        ret = [dataBase executeUpdate:sql];
        if (ret) {
            NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ where phone = %@",tableName,phone];
            ret = [dataBase executeUpdate:deleteSql];
            BOOL flag = [dataBase executeUpdate:@"insert into phoneTable (phone) values(?)",phone];
        }
    }
    return ret;
}

- (BOOL)readAllPhoneData:(NSString *)search AndDataArr:(getAllPhoneData)allDataBlock {
    BOOL ret = NO;
    NSString *dbPath = [self getPhoneDbPath];
    NSString *tableName = @"phoneTable";
    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
    if ([dataBase open]) {
        NSString *sql =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'phone' TEXT)",tableName];
        ret = [dataBase executeUpdate:sql];
        NSString *searchStr = @"";
        if (search.length == 0) {
            searchStr = [NSString stringWithFormat:@"select * from %@",tableName];
        }else {
            searchStr = [NSString stringWithFormat:@"select * from %@ where phone like '%@%%'",tableName,search];
        }
        FMResultSet *result = [dataBase executeQuery:searchStr];
        NSMutableArray *marr = [NSMutableArray array];
        while ([result next]) {
            [marr addObject:[result stringForColumn:@"phone"]];
        }
        allDataBlock(marr);
    }
    return ret;
}

#pragma mark - private function
- (NSString *)getDbPath
{
    NSString *doc= [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *path = [NSString stringWithFormat:@"%@/%@_data.sqlite", doc, kLoginName];
    return path;
}

- (NSString *)getPhoneDbPath
{
    NSString *doc= [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *path =[NSString stringWithFormat:@"%@/phone_data.sqlite", doc];
    return path;
}


@end
